package com.xbang.attendance.utils;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.CellRangeAddress;

import com.sun.xml.internal.ws.message.saaj.SAAJHeader;

/**
 * @author huawei
 * @email alonelaval@gmail.com
 */
public class HSSFUtil {
	/**
	 * 得到某一行
	 * 
	 * @param index
	 *            索引
	 * @param sheet
	 * 
	 * @return row
	 */
	public static HSSFRow getRow(int index, HSSFSheet sheet) {
		return sheet.getRow(index);
	}
	
	public static HSSFSheet getSheet(HSSFWorkbook workBook,String name){
		return workBook.getSheet(name);
	}
	/***
	 * 得到某一列
	 * 
	 * @param index
	 *            索引
	 * @param row
	 * 
	 * @return cell
	 */
	public static HSSFCell getCell(int index, HSSFRow row) {
		return row.getCell(index);
	}

	public static HSSFSheet getSheet(int index, HSSFWorkbook workBook) {
		return workBook.getSheetAt(index);
	}

	/**
	 * 设置合并单元格边框样式 //i行要是没有create的话，//会调用create方法生成后返回 //所以调用setRegionStyle方法后，
	 * 后面程序中对于合并单元格中包括的行直接使用get就可以了， 千万别使用create，不然样式又没有了。
	 * 
	 * @param sheet
	 * @param ca
	 * @param cs
	 */
	public static void setRegionStyle(HSSFSheet sheet, CellRangeAddress ca,
			HSSFCellStyle cs) {
		for (int i = ca.getFirstRow(); i <= ca.getLastRow(); i++) {
			HSSFRow row = HSSFUtil.getRow(i, sheet);
			for (int j = ca.getFirstColumn(); j <= ca.getLastColumn(); j++) {
				HSSFCell cell = HSSFUtil.getCell(j, row);
				cell.setCellStyle(cs);
			}
		}
	}
	
	/**
	 *设置自己的样式
	 * @param targetCell
	 * @param sourceCell
	 * @param sytCellStyle
	 */
	public static void copyCellAndStyle(Cell targetCell,Cell sourceCell,HSSFCellStyle sytCellStyle) {
		targetCell.setCellStyle(sytCellStyle);
		if(sytCellStyle == null){
			targetCell.setCellStyle(sourceCell.getCellStyle());
			copyCell(targetCell, sourceCell);
		}
	}

	
	/***
	 * copyCell完全的拷贝
	 * 回复最原始的表格，
	 * 无任何合并的单元格，
	 * 如果你合并，
	 * 也给你显示出来。
	 * @param targetCell
	 * @param sourceCell
	 */
	public static void copyCell(Cell targetCell,Cell sourceCell) {
		//targetCell = targetRow.createCell(j);
		//targetCell.setEncoding(sourceCell.get);
		targetCell.setCellComment(sourceCell.getCellComment());
		
		int cType = sourceCell.getCellType();
		targetCell.setCellType(cType);
		switch (cType) {
		case HSSFCell.CELL_TYPE_BOOLEAN:
			targetCell.setCellValue(sourceCell.getBooleanCellValue());
			System.out.println("--------TYPE_BOOLEAN:"
					+ targetCell.getBooleanCellValue());
			break;
		case HSSFCell.CELL_TYPE_ERROR:
			targetCell.setCellErrorValue(sourceCell.getErrorCellValue());
			System.out.println("--------TYPE_ERROR:"
					+ targetCell.getErrorCellValue());
			break;
		case HSSFCell.CELL_TYPE_FORMULA:
			// parseFormula这个函数的用途在后面说明
			targetCell
					.setCellFormula(parseFormula(sourceCell.getCellFormula()));
			System.out.println("--------TYPE_FORMULA:"
					+ targetCell.getCellFormula());
			break;
		case HSSFCell.CELL_TYPE_NUMERIC:
			targetCell.setCellValue(sourceCell.getNumericCellValue());
			System.out.println("--------TYPE_NUMERIC:"
					+ targetCell.getNumericCellValue());
			break;
		case HSSFCell.CELL_TYPE_STRING:
			targetCell.setCellValue(sourceCell.getRichStringCellValue());
			System.out.println("--------TYPE_STRING:"
					+ targetCell.getRichStringCellValue());
			break;
		}
	}
	
	private static String parseFormula(String pPOIFormula) {
		  final String cstReplaceString = "ATTR(semiVolatile)"; //$NON-NLS-1$
		  StringBuffer result = null;
		  int index;

		  result = new StringBuffer();
		  index = pPOIFormula.indexOf(cstReplaceString);
		  if (index >= 0) {
		   result.append(pPOIFormula.substring(0, index));
		   result.append(pPOIFormula.substring(index
		     + cstReplaceString.length()));
		  } else {
		   result.append(pPOIFormula);
		  }
		  return result.toString();
		 }
}
